import pymysql


class DBTools(object):
    @classmethod  #定义类方法
    def __create_conn(cls):   #设置私有属性
        conn = pymysql.connect(host='192.168.49.131', port=3306, user='root', password='123456',
                               database='test1', charset='utf8')
        return conn

    @classmethod
    # 为查询记录封装一条方法
    def query_one(cls, sql):
        my_cursor = None
        my_conn = None
        res = None

        # 创建连接，调用类名,调用类方法
        try:
            my_conn = DBTools.__create_conn()
            my_cursor = my_conn.cursor()
            my_cursor.execute(sql)
            res = my_cursor.fetchone()
        except Exception as e:
            print('执行查询失败：', str(e))
        finally:
            my_cursor.close()
            my_conn.close()
            return res


    @classmethod
    # 为增删改封装一个方法
    def db_uid(cls, sql):
        my_conn = None
        my_cursor = None
        try:
            my_conn = DBTools.__create_conn()
            my_cursor = my_conn.cursor()
            my_cursor.execute(sql)
            print('Affected rows:', my_conn.affected_rows())
            my_conn.commit()
        except Exception as e:
            print('执行增删改失败：', str(e))
            my_conn.rollback()
        finally:
            my_cursor.close()
            my_conn.close()


if __name__ == '__main__':
    result = DBTools.query_one("select * from user_profile;")
    print('查询的结果为：', result)
    DBTools.db_uid("insert into user_profile values(6,6543,'male',20,'北京大学','test2');")
    DBTools.db_uid("delete from user_profile where province = 'test2';")
    DBTools.db_uid("update user_profile set gender = 'male' where id = 5;")